-- =============================================
-- Function: CalInterestAddon
-- Purpose:	This function computes the interest (straight line) on the loan db for the month supplied in the paramater @report_date.
--			The interest is calculated based  on the interest rate supplied This procedure serves as a datasouce for the report
-- Author: Garret Stephenson
-- Created: 2013-5-10
-- Modification History
-- Modified By	Modification Date	Reason
-- =====================================================================================
--  Dale McFarlane    2014-1-29    Factor in early closed data of loans
-- =====================================================================================
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'CalInterestAddon') AND xtype IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION CalInterestAddon
GO

CREATE FUNCTION [dbo].[CalInterestAddon]
(
	@month INTEGER,
	@year INTEGER,
	@interest_rate DECIMAL(18, 2),
	@start_date DATETIME,
	@end_date DATETIME,
	@closed_date DATETIME,
	@principal DECIMAL(18, 2),
	@period VARCHAR(16)
)
RETURNS DECIMAL
AS
BEGIN
	DECLARE @interest DECIMAL, 
	@loanStartDateCurrentMonth DATETIME,
	@loanWeeklyPymtDate DATETIME,
	@report_date DATETIME
	
	SET @interest = 0
	SET @report_date = dbo.DateFromParts --end of the the month for which the report is required
	(
		@year, 
		@month, 
		DAY(DATEADD(mm, DATEDIFF(mm, -1, dbo.DateFromParts(@year, @month, 1)), -1))
	)
	
	SET @interest_rate= @interest_rate / 100;
	SET @loanStartDateCurrentMonth = @start_date;
	
	IF @closed_date IS NOT NULL
	BEGIN
		SET @end_date = @closed_date
	END
	
	If @period = 'weekly'
	BEGIN
	
		SET @loanWeeklyPymtDate = DATEADD(week, 1, @start_date)
		
		WHILE (YEAR(@loanWeeklyPymtDate) < @year OR (YEAR(@loanWeeklyPymtDate) = @year AND MONTH(@loanWeeklyPymtDate) < @month))
		BEGIN
			SET @loanWeeklyPymtDate = DATEADD(week, 1, @loanWeeklyPymtDate)
		END
		
		WHILE (YEAR(@loanWeeklyPymtDate) = @year AND MONTH(@loanWeeklyPymtDate) = @month)
		BEGIN
			IF (@end_date >=  @loanWeeklyPymtDate)
			BEGIN		
				SET @interest = @interest + (@principal * @interest_rate)
				SET @loanWeeklyPymtDate = DATEADD(week, 1, @loanWeeklyPymtDate);
			END
			ELSE
				BREAK;
		END
	END
	ELSE IF @period = 'bi-monthly'
	BEGIN
	
		SET @loanWeeklyPymtDate = DATEADD(week, 2, @start_date)
		
		WHILE (YEAR(@loanWeeklyPymtDate) < @year OR (YEAR(@loanWeeklyPymtDate) = @year AND MONTH(@loanWeeklyPymtDate) < @month))
		BEGIN
			SET @loanWeeklyPymtDate = DATEADD(week, 2, @loanWeeklyPymtDate)
		END
		
		WHILE (YEAR(@loanWeeklyPymtDate) = @year AND MONTH(@loanWeeklyPymtDate) = @month)
		BEGIN
			IF (@end_date >=  @loanWeeklyPymtDate)
			BEGIN		
				SET @interest = @interest + (@principal * @interest_rate) * 2
				SET @loanWeeklyPymtDate = DATEADD(week, 2, @loanWeeklyPymtDate);
			END
			ELSE
				BREAK;
		END
	END
	ELSE IF @period = 'monthly'
	BEGIN
			
		IF (DATEADD(month, 1, @start_date) <= @report_date AND @report_date <= @end_date) OR (YEAR(@report_date) = YEAR(@end_date) AND MONTH(@report_date) = MONTH(@end_date))
		BEGIN
			SET @interest = @principal * @interest_rate * 4
		END
	END
	RETURN @interest;
END

GO


